package com.ld.hpfs.web.controller;

import java.io.InputStream;
import java.io.PrintWriter;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.StringTokenizer;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.client.HttpClient;
import org.apache.http.client.methods.HttpGet;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.util.EntityUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.ld.hpfs.common.jackson.JackJson;
import com.ld.hpfs.common.utils.Const;
import com.ld.hpfs.common.utils.Tools;
import com.ld.hpfs.pojo.DbSourceSetting;
import com.ld.hpfs.pojo.ExtPager;
import com.ld.hpfs.pojo.ExtReturn;
import com.ld.hpfs.pojo.User;
import com.ld.hpfs.service.DbSourceSettingService;
import com.ld.hpfs.service.HistoryService;
import com.ld.hpfs.service.UploadService;
import com.ld.hpfs.web.interseptor.WebConstants;

@Controller
public class DBSettingController {
    private static final Logger    logger = LoggerFactory.getLogger(LoginController.class);

    @Autowired
    private DbSourceSettingService dbsettingService;

    @Autowired
    private UploadService          uploadService;
    
    @Autowired
    private HistoryService          historyService;

    @RequestMapping(value = "/dbsettingtest")
    @ResponseBody
    public Object getdbsettingfields(@RequestParam String sql, @RequestParam String dbtype,
            @RequestParam String dbusername, @RequestParam String dbpassword, @RequestParam String dbhost,
            @RequestParam String dbport, @RequestParam String dbinstance, @RequestParam String filterFlds,
            HttpSession session, HttpServletRequest request) {

        String driver = null;
        java.sql.Connection dbConn = null;
        Statement st = null;

        if (dbtype.equals("oracle")) {
            driver = "oracle.jdbc.driver.OracleDriver";
        } else // sqlserver
        {
            driver = "net.sourceforge.jtds.jdbc.Driver";
        }

        String connectString = getConnectString(dbtype, dbhost, dbport, dbinstance);

        try {

            Class.forName(driver);

            dbConn = DriverManager.getConnection(connectString, dbusername, dbpassword);

            st = dbConn.createStatement();

            String[] fldlist = null;
            if (filterFlds != null && filterFlds.length() > 0) {
                StringTokenizer st1 = new StringTokenizer(filterFlds, "|");
                fldlist = new String[st1.countTokens()];
                int i = 0;
                while (st1.hasMoreTokens()) {
                    fldlist[i++] = st1.nextToken();
                }
            }
            
            if (sql.toUpperCase().indexOf("WHERE") <0)
            {
            	sql += " WHERE 0>1";
            }else
            {
            	int indexOfWhere = sql.toUpperCase().indexOf("WHERE");
            	String tailer = sql.substring(indexOfWhere+"WHERE".length());
            	sql = sql.substring(0,indexOfWhere+"WHERE".length());
            	sql += "  0>1 AND ";
            	sql += tailer;
            }

            if (st.execute(sql)) {
                ResultSet rs = st.getResultSet();
                ResultSetMetaData rsmd = rs.getMetaData();
                int nCount = rsmd.getColumnCount();

                StringBuilder sb = new StringBuilder();
                sb.append("{");
                sb.append("\"success\": true,");
                sb.append("\"children\": [");

                for (int i = 1; i <= nCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    boolean ifContinue = false;
                    if (fldlist != null) {
                        for (int j = 0; j < fldlist.length; j++) {
                            if (columnName.equals(fldlist[j])) {
                                ifContinue = true;
                                break;
                            }
                        }
                    }

                    if (ifContinue)
                        continue;

                    int columnType = rsmd.getColumnType(i);
                    String strColumnType = "";

                    if (columnType == java.sql.Types.ARRAY) {
                        strColumnType = "ARRAY";
                    } else if (columnType == java.sql.Types.BIGINT) {
                        strColumnType = "BIGINT";
                    } else if (columnType == java.sql.Types.BINARY) {
                        strColumnType = "BINARY";
                    } else if (columnType == java.sql.Types.BIT) {
                        strColumnType = "BIT";
                    } else if (columnType == java.sql.Types.BOOLEAN) {
                        strColumnType = "BOOLEAN";
                    } else if (columnType == java.sql.Types.BLOB) {
                        strColumnType = "BLOB";
                    } else if (columnType == java.sql.Types.CHAR) {
                        strColumnType = "CHAR";
                    } else if (columnType == java.sql.Types.CLOB) {
                        strColumnType = "CLOB";
                    } else if (columnType == java.sql.Types.DATALINK) {
                        strColumnType = "DATALINK";
                    } else if (columnType == java.sql.Types.DATE) {
                        strColumnType = "DATE";
                    } else if (columnType == java.sql.Types.DECIMAL) {
                        strColumnType = "DECIMAL";
                    } else if (columnType == java.sql.Types.DISTINCT) {
                        strColumnType = "DISTINCT";
                    } else if (columnType == java.sql.Types.DOUBLE) {
                        strColumnType = "DOUBLE";
                    } else if (columnType == java.sql.Types.FLOAT) {
                        strColumnType = "FLOAT";
                    } else if (columnType == java.sql.Types.INTEGER) {
                        strColumnType = "INTEGER";
                    } else if (columnType == java.sql.Types.JAVA_OBJECT) {
                        strColumnType = "JAVA_OBJECT";
                    } else if (columnType == java.sql.Types.LONGNVARCHAR) {
                        strColumnType = "LONGNVARCHAR";
                    } else if (columnType == java.sql.Types.LONGVARBINARY) {
                        strColumnType = "LONGVARBINARY";
                    } else if (columnType == java.sql.Types.LONGVARCHAR) {
                        strColumnType = "LONGVARCHAR";
                    } else if (columnType == java.sql.Types.NCHAR) {
                        strColumnType = "NCHAR";
                    } else if (columnType == java.sql.Types.NCLOB) {
                        strColumnType = "NCLOB";
                    } else if (columnType == java.sql.Types.NUMERIC) {
                        strColumnType = "NUMERIC";
                    } else if (columnType == java.sql.Types.NVARCHAR) {
                        strColumnType = "NVARCHAR";
                    } else if (columnType == java.sql.Types.REAL) {
                        strColumnType = "REAL";
                    } else if (columnType == java.sql.Types.SMALLINT) {
                        strColumnType = "SMALLINT";
                    } else if (columnType == java.sql.Types.SQLXML) {
                        strColumnType = "SQLXML";
                    } else if (columnType == java.sql.Types.TIME) {
                        strColumnType = "TIME";
                    } else if (columnType == java.sql.Types.TIMESTAMP) {
                        strColumnType = "TIMESTAMP";
                    } else if (columnType == java.sql.Types.TINYINT) {
                        strColumnType = "TINYINT";
                    } else if (columnType == java.sql.Types.VARBINARY) {
                        strColumnType = "VARBINARY";
                    } else if (columnType == java.sql.Types.VARCHAR) {
                        strColumnType = "VARCHAR";
                    } else {
                        strColumnType = "UNKNOWN";
                    }
                    sb.append("{\"columnname\":\"").append(columnName).append("\",\"datatype\":\"")
                            .append(strColumnType).append("\"}");

                    if (i < nCount) {
                        sb.append(",");
                    }
                }

                sb.append("]");
                sb.append("}");

                return sb.toString();
            }
        } catch (Exception e) {

            e.printStackTrace();

        } finally {
            try {
                if (st != null) {
                    st.close();
                }

                if (dbConn != null) {
                    dbConn.close();
                }
            } catch (Exception ex) {

            }
        }

        return new ExtReturn(false, "无法连接到数据库或查询数据失败!");
    }

    //
    private String getConnectString(String dbtype, String dbhost, String dbport, String dbinstance) {
        if (dbtype == null) {
            dbtype = "sqlserver";
        }

        StringBuffer jdbcurl = null;

        if (dbtype.equals("oracle")) {

            jdbcurl = new StringBuffer("jdbc:oracle:thin:@");
            jdbcurl.append(dbhost).append(":").append(dbport).append(":").append(dbinstance);
        } else // sqlserver
        {
            jdbcurl = new StringBuffer("jdbc:jtds:sqlserver://");
            jdbcurl.append(dbhost).append(":").append(dbport).append(";DatabaseName=").append(dbinstance);

        }

        return jdbcurl.toString();
    }

    @RequestMapping(value = "/adddbsetting")
    @ResponseBody
    public Object adddbsettingfields(@RequestParam String sql, @RequestParam String dbtype,
            @RequestParam String dbusername, @RequestParam String dbpassword, @RequestParam String dbhost,
            @RequestParam String dbport, @RequestParam String dbinstance, @RequestParam String srcColumns,
            @RequestParam String destColumns, @RequestParam String dbdescriptor, HttpSession session,
            HttpServletRequest request) {

        User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
        Object currentCompany = session.getAttribute(WebConstants.CURRENT_COMPANY);
        DbSourceSetting dbsetting = new DbSourceSetting();
        String connectString = this.getConnectString(dbtype, dbhost, dbport, dbinstance);
        // dbsetting.setCompanyId(currentCompany);
        dbsetting.setCompanyId(Integer.parseInt(currentCompany.toString()));
        // here to be modify
        dbsetting.setDbconnectstring(connectString);
        dbsetting.setDbhostname(dbhost);
        dbsetting.setDbport(dbport);
        dbsetting.setDbsourceType(dbtype);
        dbsetting.setDbpassword(dbpassword);
        dbsetting.setDbusername(dbusername);
        dbsetting.setInstance(dbinstance);
        dbsetting.setDbdesc(dbdescriptor);
        dbsetting.setDbsql(sql);
        dbsetting.setCreatorId(user.getUserId());
        String mappingString = "";

        StringTokenizer st1 = new StringTokenizer(srcColumns, "|");
        StringTokenizer st2 = new StringTokenizer(destColumns, "|");
        String[] srcFlds = new String[st1.countTokens()];
        String[] destFlds = new String[st2.countTokens()];

        int i = 0;
        while (st1.hasMoreTokens()) {
            String srcFld = st1.nextToken();
            srcFlds[i++] = srcFld;
        }

        i = 0;
        while (st2.hasMoreTokens()) {
            String destFld = st2.nextToken();
            destFlds[i++] = destFld;
        }

        if (srcFlds == null || destFlds == null || srcFlds.length != destFlds.length) {
            return "";
        }

        for (i = 0; i < srcFlds.length; i++) {
            String srcFld = srcFlds[i];
            String destFld = destFlds[i];
            String mappingfld = srcFld + ":" + destFld;
            mappingString += mappingfld;
            if (i < srcFlds.length - 1) {
                mappingString += ";";
            }
        }

        dbsetting.setDbcolumnMapping(mappingString);

        int iResult = dbsettingService.addDbSourceSetting(dbsetting);
        return new ExtReturn(true, "success");
    }

    // 查询dbsetting 列表
    @RequestMapping(value = "/getdbsettings")
    @ResponseBody
    public Object getdbsetting(ExtPager page, HttpSession session, HttpServletRequest request) {

        Object currentCompany = session.getAttribute(WebConstants.CURRENT_COMPANY);
        if (currentCompany == null) {
            return new ExtReturn(false, "请先登录!");
        }
        //String sort = (String) request.getParameter("sort") == null ? null : (String) request.getParameter("sort");
        int pageStart = page.getStart();
        int pageEnd = page.getStart() + page.getLimit();
        String orderby = "DS_NAME";      
        //String direction = "ASC";
        ArrayList<Map<String, String>> field = page.getSortField();
		StringBuffer sort = new StringBuffer("");
		if(field!=null && field.size()>0){
		    for(Map<String, String> map : field){
		    	String property = map.get("property");
		    	if (property.equals("dbdescriptor"))
		    	{
		    		property = "DS_NAME";
		    	}else if (property.equals("dbtype"))
		    	{
		    		property = "DS_TYPE";
		    	}else if (property.equals("dbusername"))
		    	{
		    		property = "DS_USERNAME";
		    	}else if (property.equals("dbpassword"))
		    	{
		    		property = "DS_PASSWORD";
		    	}else if (property.equals("dbhost"))
		    	{
		    		property = "DS_IP";
		    	}else if (property.equals("dbport"))
		    	{
		    		property = "DS_PORT";
		    	}else if (property.equals("dbinstance"))
		    	{
		    		property = "DS_DB_SID";
		    	}
		    	sort.append(property + " " + map.get("direction") + ",");
		    }
		    orderby = sort.substring(0,sort.length()-1);
		} 

        HashMap<String, Object> param = new HashMap<String, Object>();
        param.put("pageStart", pageStart);
        param.put("pageEnd", pageEnd);
        param.put("orderBy", orderby);

        param.put("id", Integer.parseInt(currentCompany.toString()));
        // currentCompany = "1";
        List<DbSourceSetting> dbsettings = dbsettingService.getDbSourceSettings(param);
        // List<DbSourceSetting> dbsettings = new
        // java.util.ArrayList<DbSourceSetting>();
        int count = param.get("pageCount") == null ? 0 : Integer.parseInt(param.get("pageCount").toString());
        return formatDBSettingResult(dbsettings, count);

    }

    private String formatDBSettingResult(List<DbSourceSetting> settings, int count) {
        StringBuilder sb = new StringBuilder();
        sb.append("{");
        sb.append("\r\n");
        sb.append("\"success\": true,");
        sb.append("\r\n");
        sb.append("\"total\":").append(count).append(",");
        sb.append("\r\n");
        sb.append("\"children\": [");
        sb.append("\r\n");
        if (settings != null) {
            for (int i = 0; i < settings.size(); i++) {
                DbSourceSetting setting = settings.get(i);
                int id = setting.getId();
                String dbtype = setting.getDbsourceType();
                String dbhost = setting.getDbhostname();
                String dbport = setting.getDbport();
                String dbusername = setting.getDbusername();
                String dbpassword = setting.getDbpassword();
                String dbinstance = setting.getInstance();
                String dbdescriptor = setting.getDbdesc();
                String columnMapping = setting.getDbcolumnMapping();
                String dbsql = setting.getDbsql();
                String[] mappings = columnMapping.split(";");

                String srcColumns = "";
                String destColumns = "";

                if (mappings != null) {
                    int index = -1;
                    for (int j = 0; j < mappings.length; j++) {
                        String mapping = mappings[j];
                        if (mapping == null || mapping.length() == 0 || (index = mapping.indexOf(":")) <= 0) {
                            continue;
                        }

                        String srcColumn = mapping.substring(0, index);
                        String destColumn = mapping.substring(index + 1);
                        srcColumns += srcColumn;
                        destColumns += destColumn;

                        if (j < (mappings.length - 1)) {
                            srcColumns += "|";
                            destColumns += "|";
                        }
                    }
                }

                sb.append("{\"id\":\"").append(id).append("\",").append("\"dbtype\":\"").append(dbtype).append("\",")
                        .append("\"dbhost\":\"").append(dbhost).append("\",").append("\"dbport\":\"").append(dbport)
                        .append("\",").append("\"dbusername\":\"").append(dbusername).append("\",")
                        .append("\"dbpassword\":\"").append(dbpassword).append("\",").append("\"dbinstance\":\"")
                        .append(dbinstance).append("\",").append("\"dbsql\":\"").append(dbsql).append("\",")
                        .append("\"dbdescriptor\":\"").append(dbdescriptor).append("\",").append("\"srcColumns\":\"")
                        .append(srcColumns).append("\",").append("\"destColumns\":\"").append(destColumns).append("\"")
                        .append("}");

                if (i < settings.size() - 1) {
                    sb.append(",");
                }

                sb.append("\r\n");
            }
        }
        sb.append("]");
        sb.append("\r\n");
        sb.append("}");

        return sb.toString();
    }

    @RequestMapping(value = "/deletedbsettings")
    @ResponseBody
    public Object deletedbsettings(@RequestParam String ids, HttpSession session, HttpServletRequest request) {
        if (ids != null && ids.length() > 0) {
            StringTokenizer st = new StringTokenizer(ids, "|");
            String[] idlist = new String[st.countTokens()];
            
            User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
            
         // 用于添加history表
         	HashMap<String, Object> param = new HashMap<String, Object>();
         	if(user != null){
         		param.put("update_id", Integer.parseInt(user.getUserId()));
         		param.put("update_name", user.getAccount());
         	}
         	
            
            int i = 0;
            while (st.hasMoreTokens()) {
                idlist[i++] = st.nextToken();
            }
            for (i = 0; i < idlist.length; i++) {
            	
            	// 用于添加history表
            	param.put("id", Integer.parseInt(idlist[i]));
            	historyService.addInputDbSourceHistory(param, false);
            	
                dbsettingService.deleteDbSourceSetting(Integer.parseInt(idlist[i]));
            }
        }

        return new ExtReturn(true, "success");
    }

    @RequestMapping(value = "/updatedbsetting")
    @ResponseBody
    public Object updatedbsettingfields(@RequestParam String sql, @RequestParam String dbtype,
            @RequestParam String dbusername, @RequestParam String dbpassword, @RequestParam String dbhost,
            @RequestParam String dbport, @RequestParam String dbinstance, @RequestParam String srcColumns,
            @RequestParam String destColumns, @RequestParam String dbdescriptor, @RequestParam String id,
            HttpSession session, HttpServletRequest request) {

        Integer currentCompany = (Integer)session.getAttribute(WebConstants.CURRENT_COMPANY);
        DbSourceSetting dbsetting = new DbSourceSetting();
        String connectString = this.getConnectString(dbtype, dbhost, dbport, dbinstance);
        // dbsetting.setCompanyId(currentCompany);
        dbsetting.setCompanyId(currentCompany);
        // here to be modify
        dbsetting.setDbconnectstring(connectString);
        dbsetting.setDbhostname(dbhost);
        dbsetting.setDbport(dbport);
        dbsetting.setDbsourceType(dbtype);
        dbsetting.setDbpassword(dbpassword);
        dbsetting.setDbusername(dbusername);
        dbsetting.setInstance(dbinstance);
        dbsetting.setDbdesc(dbdescriptor);
        dbsetting.setDbsql(sql);
        String mappingString = "";

        StringTokenizer st1 = new StringTokenizer(srcColumns, "|");
        StringTokenizer st2 = new StringTokenizer(destColumns, "|");
        String[] srcFlds = new String[st1.countTokens()];
        String[] destFlds = new String[st2.countTokens()];

        int i = 0;
        while (st1.hasMoreTokens()) {
            String srcFld = st1.nextToken();
            srcFlds[i++] = srcFld;
        }

        i = 0;
        while (st2.hasMoreTokens()) {
            String destFld = st2.nextToken();
            destFlds[i++] = destFld;
        }

        if (srcFlds == null || destFlds == null || srcFlds.length != destFlds.length) {
            return "";
        }

        for (i = 0; i < srcFlds.length; i++) {
            String srcFld = srcFlds[i];
            String destFld = destFlds[i];
            String mappingfld = srcFld + ":" + destFld;
            mappingString += mappingfld;
            if (i < srcFlds.length - 1) {
                mappingString += ";";
            }
        }

        dbsetting.setDbcolumnMapping(mappingString);
        dbsetting.setId(Integer.parseInt(id));
        
     // 用于添加history表
     HashMap<String, Object> hist_param = new HashMap<String, Object>();
     hist_param.put("id", Integer.parseInt(id));
     User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
     if(user != null){
    	 hist_param.put("update_id", Integer.parseInt(user.getUserId()));
    	 hist_param.put("update_name", user.getAccount());
     }
     historyService.addInputDbSourceHistory(hist_param, true);
        
        int iResult = dbsettingService.updateDbSourceSetting(dbsetting);
        return new ExtReturn(true, "success");
    }

    @RequestMapping(value = "/adddatasourceimporttask")
    @ResponseBody
    public void updatedbsettingfields(@RequestParam String id, HttpSession session, HttpServletRequest request,
            PrintWriter writer) {
        try {
            User user = (User) session.getAttribute(WebConstants.CURRENT_USER);
            HashMap<String, Object> param = new HashMap<String, Object>();

            StringBuilder code = new StringBuilder();
            code.append(session.getAttribute(WebConstants.CURRENT_COMPANY)).append(user.getUserId())
                    .append(System.currentTimeMillis());
            param.put("companyId", session.getAttribute(WebConstants.CURRENT_COMPANY).toString());
            param.put("type", WebConstants.IMPORT_TASK_TYPE_SQL);
            param.put("schemaId", id);
            // User user =
            // (User)request.getSession().getAttribute(WebConstants.CURRENT_USER);
            param.put("user_id", user.getUserId());
            param.put("create_time", Tools.getCurrentDay());
            param.put("status", WebConstants.TASK_STATUS_IMPORTED);
            param.put("code", code.toString());
            uploadService.insertImportLog(param);
            String lang = session.getAttribute(WebConstants.CURRENT_LANG).toString();
            StringBuilder impUrl = new StringBuilder();
            impUrl.append(Const.PROPERTIES.getProperty("import.server.url"))
                    .append(Const.PROPERTIES.getProperty("import.server.import_method")).append(code.toString()).append("&lang=").append(lang);
            logger.debug("Import URL = {}", impUrl);
            HttpClient httpclient = new DefaultHttpClient();
            HttpGet httpget = new HttpGet(impUrl.toString());
            HttpResponse response = httpclient.execute(httpget);
            HttpEntity entity = response.getEntity();
            if (entity != null) {
                InputStream instream = entity.getContent();
                try {
                    String res = EntityUtils.toString(entity);
                    logger.debug(res);
                    Map<String, Object> retMap = JackJson.fromJsonToObject(res, Map.class);
                    String resultCode = retMap.get("ResultCode").toString();
                } finally {
                    instream.close();
                }
            }

            String returnMsg = JackJson.fromObjectToJson(new ExtReturn(true, "inserty task successfully!"));
            writer.print(returnMsg);
        } catch (Exception e) {
            logger.error("Exception: {}", e);
        } finally {
            writer.flush();
            writer.close();
        }
    }
}
